package us.yesor.atlast.framework;

import us.yesor.atlast.InteractionTable;
import us.yesor.atlast.ItemInteraction;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class AtLastDbAdapter {

	// Database column headings receive the assigned String values
	private static final String KEY_ROW = "_id";
	private static final String KEY_COLLECTION = "collection";
	private static final String KEY_ITEM = "item";
	private static final String KEY_TRIGGER = "trigger";
	private static final String KEY_VALUE = "value";
	private static final String KEY_STATE = "state";
	private static final String KEY_TIME = "time";


	private static final String TAG = "AtLastDbAdapter";
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	private static final String CREATE_INTERACTION_TABLE = "create table interaction "
			+ "(_id integer primary key autoincrement, collection text, item text, "
			+ "trigger text, value text, state text, time text);";
	
	private static final String DATABASE_NAME = "AtLastDB";
	private static final String DATABASE_INTERACTIONS_TABLE = "interactions";
	private static final int DATABASE_VERSION = 1;

	private final Context mCtx;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL("DROP TABLE IF EXISTS "+InteractionTable.TABLE_NAME);
			db.execSQL(CREATE_INTERACTION_TABLE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS "+InteractionTable.TABLE_NAME);
			onCreate(db);
		}
	}

	public AtLastDbAdapter(Context ctx) {
		this.mCtx = ctx;
	}

	public Context getContext() {
		return mCtx;
	}

	public void open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		Log.i(TAG, "Opened an AtLast database in context " + mCtx.toString());
	}

	public void overWrite() {
		mDb.execSQL("DROP TABLE IF EXISTS interaction");
		mDb.execSQL(CREATE_INTERACTION_TABLE);
	}

	public void close() {
		mDbHelper.close();
		mDb.close();
		Log.i(TAG, "Closed the database from context "
				+ mCtx.toString());
	}
	
	public long insertInteraction(ItemInteraction interaction) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_COLLECTION, interaction.getCollection());
		initialValues.put(KEY_ITEM, interaction.getItem());
		initialValues.put(KEY_TRIGGER, interaction.getTrigger());
		initialValues.put(KEY_VALUE, interaction.getValue());
		initialValues.put(KEY_STATE, interaction.getState());
		initialValues.put(KEY_TIME, interaction.getTime().toString());
		Log.i(TAG, "Inserting interactions info: "+ initialValues.toString());
		return mDb.insert(DATABASE_INTERACTIONS_TABLE, null, initialValues);
	}
	
	public boolean deleteInteraction(long rowId) {
		return mDb.delete(DATABASE_INTERACTIONS_TABLE, KEY_ROW + "=" + rowId, null) > 0;
	}

	/*
	 * Returns the most recent interaction data for each object.
	 */
	public Cursor getItemsByCollection(String collection) {	
		String selection = "SELECT _id, collection, item, trigger, value, state, MAX(time) FROM interactions "
				+" WHERE item in (SELECT DISTINCT item FROM interactions WHERE collection ='"+collection+"') GROUP BY item";
		Cursor c = mDb.rawQuery(selection, null);
		Log.i(TAG," Returning "+c.getCount()+" existing items from the "+collection+" collection.");
		return c;
	}
	
	/*
	 *  Returns the local history of interactions for an object 
	 */
	public Cursor getItemHistory(String item) {
		String selection = "SELECT _id, collection, item, trigger, value, state, time FROM interactions "
				+ " WHERE item = '"+item+"' ORDER BY time";
		Cursor c = mDb.rawQuery(selection, null);
		Log.i(TAG, "Returning "+c.getCount()+" interactions for item "+item);
		return c;
	}

	/*
	 * Returns the existing collections in the database
	 */
	public Cursor getExistingCollections() {
		String selection = "SELECT _id, collection, MAX(time) FROM interactions "
				+" WHERE collection IN (SELECT DISTINCT collection FROM interactions) GROUP BY collection";
		Cursor c = mDb.rawQuery(selection, null);
		Log.i(TAG, "Returning "+c.getCount()+" collections from the database.");
		return c;
	}

}
